package com.study.jdbc.junit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.study.jdbc.domain.Item;
import com.study.jdbc.utils.DBUtil;

/**
* @Name: JDBCTest
* @Description: jdbc连接测试类
* @Author: 李超	lc_jack@foxmail.com
* @CreateDate: 2017-2-16 下午2:46:26 
* @Version: V1.0
 */
public class JDBCTest {
	
	@Test
	public void test01() {
		//1、加载数据库驱动程序
		try {
			Class.forName("com.mysql.jdbc.Driver") ;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection con = null ;
		Statement s = null ;
		ResultSet rs = null ;
		try {
			//2、获取数据库连接对象
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "P@ssw0rd") ;
			//3、创建sql语句执行对象
			s = con.createStatement() ;
			//4、执行sql语句，获取结果集对象，遍历结果集
			String sql = "select id, name, price, detail, pic, createtime from tb_items ; " ;
			rs = s.executeQuery(sql) ;
			while(rs.next()) {
				System.out.println("=====================================");
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getString("name"));
				System.out.println(rs.getFloat("price"));
				System.out.println(rs.getString("detail"));
				System.out.println(rs.getString("pic"));
				System.out.println(rs.getDate("createtime"));
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//5、释放资源
			if(con != null) {
				try {
					con.close() ;
				} catch (Exception e) {
					e.printStackTrace() ;
				} finally {
					con = null ;
				}
			}
			if(s != null) {
				try {
					s.close() ;
				} catch (Exception e) {
					e.printStackTrace() ;
				} finally {
					s = null ;
				}
			}
			if(rs != null) {
				try {
					rs.close() ;
				} catch (Exception e) {
					e.printStackTrace() ;
				} finally {
					rs = null ;
				}
			}
		}
	}
	
	
	@Test
	public void test02() {
		Connection con = null ;
		Statement s = null ;
		ResultSet rs = null ;
		List<Item> items = null ;
		try {
			//1、获取数据库连接对象
			con = DBUtil.getConnection() ;
			//2、创建sql语句执行对象
			s = con.createStatement() ;
			//3、执行sql语句，获取结果集对象，将查询结果存入到Item对象中
			String sql = "select id, name, price, detail, pic, createtime from tb_items ; " ;
			rs = s.executeQuery(sql) ;
			items = new ArrayList<Item>() ;
			while(rs.next()) {
				Item item = new Item() ;
				item.setId(rs.getInt("id")) ;
				item.setName(rs.getString("name")) ;
				item.setPrice(rs.getFloat("price")) ;
				item.setDetail(rs.getString("detail")) ;
				item.setCreatetime(rs.getDate("createtime")) ;
				items.add(item) ;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//4、释放资源
			DBUtil.close(con, s, rs) ;
		}
		//打印查询结果
		for (Item item : items) {
			System.out.println(item);
		}
	}
	
	

}
